﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class MVTINVOICEData : BaseFunction
    {
        
        public MVTINVOICEData()
            : base()
        {

        }
        public MVTINVOICEData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetINVOICE(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "INVOICE";
            string PI = dwABPOS["PI"].ToString().Trim();
            string AGENT = dwABPOS["AGENT"].ToString().Trim();
            string INVOICE = dwABPOS["INVOICE"].ToString().Trim();
            string KUNDNR = dwABPOS["KUNDNR"].ToString().Trim();
            string STATUS = dwABPOS["STATUS"].ToString().Trim();
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT D.KDKUNDNA,A.VKPAUFKRS,A.VKPAUFLFD,A.VKPAUFPOS, "
                    + " A.VKPAUFKRS||'-'||TO_NUMBER(A.VKPAUFLFD)||'-'||TRIM(D.KDKUNDNA) AS PI, "
                    + " E.GLOAMICUC*0.01 AS ADPAY,B.VRKRENR AS RE,C.VRPREPOS,B.VRKWAEHRSL, "
                    + " H.VRPRENR AS GU,G.VRPRENR AS GS,C.VRPLIEFSCH,B.VRKKOMPL,C.VRPGESWERT*0.001 AS INVOICE, "
                    + " C.VRPART,C.VRPFARBE,C.VRPMENGE*0.01 AS VRQTY,C.VRPMEEH, "
                    + " C.VRPPREIS*0.001 AS VRPRICE,C.VRPPREH,S.VEVERTNA AS AGENT,C.VRPPROVP1*0.0001 AS RATE, "
                    + " H.VA AS ENDINVOICE,G.VA AS AGINVOICE, "
                    + " DECODE(C.VRPVERTNR1,'        ',0,(CASE WHEN C.VRPPROVP1=0  "
                    + " THEN C.VRPGESWERT*0.001-G.VA ELSE C.VRPGESWERT*0.001*C.VRPPROVP1*0.0001 END )) AS COMMISSION "
                    + " FROM VKPOS A,VRKOPF B,VRPOS C,KDSTAMM D,GLOPITEM E,VESTAMM S, "
                    + " (SELECT A1.VRPAUFKRS,A1.VRPAUFLFD,A1.VRPAUFPOS,A1.VRPLIEFSCH, "
                    + " A1.VRPRRENR,A1.VRPRREPOS,A1.VRPGESWERT*0.001 AS VA,A1.VRPRENR "
                    + " FROM VRPOS A1,VRKOPF B1 WHERE A1.VRPMANDANT = B1.VRKMANDANT "
                    + " AND A1.VRPRENR = B1.VRKRENR AND B1.VRKREART = 'GU' AND A1.VRPSTORNO <>'J') H, "
                    + " (SELECT A1.VRPAUFKRS,A1.VRPAUFLFD,A1.VRPAUFPOS,A1.VRPLIEFSCH, "
                    + " A1.VRPRRENR,A1.VRPRREPOS,A1.VRPGESWERT*0.001 AS VA,A1.VRPRENR "
                    + " FROM VRPOS A1,VRKOPF B1 WHERE A1.VRPMANDANT = B1.VRKMANDANT "
                    + " AND A1.VRPRENR = B1.VRKRENR AND B1.VRKREART = 'GS' AND A1.VRPSTORNO <>'J') G "
                    + " WHERE A.VKPMANDANT = C.VRPMANDANT AND A.VKPAUFKRS = C.VRPAUFKRS "
                    + " AND A.VKPAUFLFD = C.VRPAUFLFD AND A.VKPAUFPOS = C.VRPAUFPOS "
                    + " AND A.VKPMANDANT = D.KDMANDANT AND A.VKPKUNDNR = D.KDKUNDNR "
                    + " AND A.VKPMANDANT = E.GLOMANDANT(+) AND RPAD(TO_NUMBER(A.VKPKUNDNR),8,' ') = E.GLOACCOUNT(+) "
                    + " AND RPAD(A.VKPAUFKRS||TO_NUMBER(A.VKPAUFLFD),10,' ') = E.GLOITEM(+) "
                    + " AND C.VRPMANDANT = B.VRKMANDANT AND C.VRPRENR = B.VRKRENR "
                    + " AND B.VRKREART = 'RE' AND C.VRPSTORNO <>'J' AND A.VKPSA <> 'AJ' "
                    + " AND C.VRPMANDANT = S.VEMANDANT(+) AND C.VRPVERTNR1 = S.VEVERTNR(+) "
                    + " AND C.VRPRENR = H.VRPRRENR(+) AND C.VRPREPOS = H.VRPRREPOS(+)  "
                    + " AND C.VRPAUFKRS = H.VRPAUFKRS(+) AND C.VRPAUFLFD = H.VRPAUFLFD(+) "
                    + " AND C.VRPAUFPOS = H.VRPAUFPOS(+) AND C.VRPLIEFSCH = H.VRPLIEFSCH(+) "
                    + " AND C.VRPRENR = G.VRPRRENR(+) AND C.VRPREPOS = G.VRPRREPOS(+)  "
                    + " AND C.VRPAUFKRS = G.VRPAUFKRS(+) AND C.VRPAUFLFD = G.VRPAUFLFD(+) "
                    + " AND C.VRPAUFPOS = G.VRPAUFPOS(+) AND C.VRPLIEFSCH = G.VRPLIEFSCH(+) "
                    + " AND (trim(D.KDKUNDNA) = '" + KUNDNR + "' OR '" + KUNDNR + "' = 'AAA' ) "
                    + " AND (A.VKPNEUDAT >= " + FROMDATE + " AND  A.VKPNEUDAT <= " + ENDDATE + ") "
                    + " AND (A.VKPAUFKRS||'-'||TO_NUMBER(A.VKPAUFLFD)||'-'||TRIM(D.KDKUNDNA) = '" + PI + "' OR '" + PI + "' = 'AAA' ) "
                    + " AND (trim(S.VEVERTNA) = '" + AGENT + "' OR '" + AGENT + "' = 'AAA' ) "                    
                    + " AND (B.VRKKOMPL = '" + STATUS + "' OR '" + STATUS + "' = 'AAA' ) "
                    + " AND (TO_NUMBER(C.VRPRENR) = '" + INVOICE + "' OR '" + INVOICE + "' = 'AAA' ) "
                    + " ORDER BY 1,2,3,4 "
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    /*byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["EKPLSART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["EKPLSART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["EKPLSFB"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["EKPLSFB"] = value;
                    }*/
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        
        public DataSet BuildMVTINVOICEData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetINVOICE(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}